/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.pdx.cs.guavadata;

import edu.pdx.cs.guavadata.condition.Condition;
import edu.pdx.cs.guavadata.data.DMLType;
import edu.pdx.cs.guavadata.data.Schema;
import edu.pdx.cs.guavadata.domain.Domain;
import edu.pdx.cs.guavadata.query.Command;
import edu.pdx.cs.guavadata.query.Delete;
import edu.pdx.cs.guavadata.query.Filter;
import edu.pdx.cs.guavadata.query.Insert;
import edu.pdx.cs.guavadata.query.Operator;
import edu.pdx.cs.guavadata.query.RowConstant;
import edu.pdx.cs.guavadata.query.TableRef;
import edu.pdx.cs.guavadata.query.Update;
import edu.pdx.cs.guavadata.subclass.ConditionType;
import edu.pdx.cs.guavadata.subclass.DataColumn;
import edu.pdx.cs.guavadata.subclass.DataSet;
import edu.pdx.cs.guavadata.subclass.DataTable;
import edu.pdx.cs.guavadata.subclass.GuavaForm;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

/**
 *
 * @author VuTran
 */
public class AppData {

    public static DataSet ds;
    public static GTree gt;
    public static Schema sch;
    public static Channel c;
    public static boolean useDB = true;
    public static String dbsource, dbcatalog;
    public static String assemblyPath;
    public static boolean testForEnum = false;

    public static void Init(GuavaForm root, UUID g, String path, String source, String catalog, Channel chan) {
        DBConnection.initialize(source, catalog, null);
        assemblyPath = path;
        gt = GTree.generate(root);
        sch = gt.toSchema();

        ds = gt.toDataSet();
        dbsource = source;
        dbcatalog = catalog;
        c = chan;

        gt.saveToXml("gtree_log.xml");
        SchemaController sc = new SchemaController(path, "schema_log.xml", sch, c);
        sc.retrieveDBSchema(sch);
        c.poseTransaction(sc.createSchemaUpdateTransaction());
        // Make sure that there is the right row for the root node

        fetchRowOrCreate(root.getGName(), g);
        c.preseedQueries(ds);
    }


    public static ResultSet fetchAllRows(String table) {
        return null;
    }

    /*
     * When the form fist launch, create a new row in the database
     *      which has only ID, and retrive back to schema to in-mem schema
     * Then if user click Ok -> Update the row
     * If User click Cancel -> Delete that row
     */
    public static int fetchRowOrCreate(String table, UUID id) {
        DataTable dt = ds.getTable(table);
        for (int i = 0; i < dt.rows.size(); i++) {
            if (id.toString().equalsIgnoreCase(dt.rows.get(i).get("id").toString()))
                return i;
        }

        if (!useDB) return createRow(table, id);

         // If it gets here, the row is not in memory, so we need to fetch from the DB
        // Create the appropriate query
        Command com = new Command();
        String alias = com.nextTableAlias(table);
        TableRef tr = new TableRef(table, alias);
        Filter f = new Filter(tr);
        com.root = f;

        try {
            for (int j = 0; j < dt.columns.size(); j++) {
                tr.addColumn(dt.columns.get(j).getName(), alias + "_" + dt.columns.get(j).getName(),
                        (Domain) dt.columns.get(j).extendedProperties.get(Operator.domainFlag));
                if (dt.columns.get(j).getName().equals("id")) {
                    f.addCondition((alias.equals("") ? alias + ".":"") + dt.columns.get(j).getName(),
                            Condition.newCondition(ConditionType.EQ, id));
                }
            }
            ResultSet res = c.poseQueryCommand(com);
            // -- ignore checking the res has only column
            // if there is no row found, so we're creating one
            if (!res.next()) {
                // add new row - with the built-in column
                Map<String, Object> drNew = new HashMap<String, Object>();
                for (int j = 0; j < dt.columns.size(); j++) {
                    drNew.put(dt.columns.get(j).getName(), null);
                }
                dt.rows.add(drNew);
                drNew.put("id", id);
                commitRow(table, id, DMLType.INSERT);
                return dt.rows.size() - 1;
            }
        

            // Yank the row from the result and add it to the in-memory database
            Map<String, Object> drNew = new HashMap<String, Object>();
            for (int i = 0; i < dt.columns.size(); i++) {
                drNew.put(dt.columns.get(i).getName(), res.getObject(i + 1));
                //System.err.println(res.getObject(i + 1).toString());
            }
            dt.rows.add(drNew);
            return dt.rows.size() - 1;
        } catch (Exception e) {
            System.err.println("ERROR: " + e.getMessage());
        }
        return 0;
    }

    public static void fetchRowsByFK(String table, UUID fk) {
        if (!useDB)
            return;
        DataTable dt = ds.getTable(table);

        // Create the appropriate query: select * from table
        Command com = new Command();
        String alias = com.nextTableAlias(table);
        TableRef tr = new TableRef(table, alias);
        for (int j = 0; j < dt.columns.size(); j++) {
            tr.addColumn(dt.columns.get(j).getName(), alias + "_" + dt.columns.get(j).getName(),
                    (Domain) dt.columns.get(j).extendedProperties.get(Operator.domainFlag));
        }

        
         Filter f = new Filter(tr);
         try {
            for (int j = 0; j < dt.columns.size(); j++) {
                if (dt.columns.get(j).getName().equals("fk")) {
                    f.addCondition(alias + "." + dt.columns.get(j).getName(),
                            Condition.newCondition(ConditionType.EQ, fk));
                }
            }
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }

        com.root = f;
        ResultSet res = c.poseQueryCommand(com);
        
        // Verify that there is only one table return

        // remove existing entries in the table
        for (int j = 0; j < dt.rows.size(); j++) {
            while ((j < dt.rows.size()) && dt.rows.get(j).get("fk").toString().equalsIgnoreCase(fk.toString()))
                dt.rows.remove(j);
        }


        // Yank the rows from the result and add it to the in-memory database
        try {
            // Yank the row from the result and add it to the in-memory database
            while (res.next()) {
                Map<String, Object> drNew = new HashMap<String, Object>();
                for (int i = 0; i < dt.columns.size(); i++) {
                    drNew.put(dt.columns.get(i).getName(), res.getObject(i + 1));
                    //System.err.println(res.getObject(i + 1).toString());
                }
                dt.rows.add(drNew);
            }
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }

        return;
    }

    public static void commitRow(String table, UUID id, DMLType t) {
        // If we're not using the DB, exit
        if (!useDB) return;

        // Grab the right row from the right table
        DataTable dt = ds.getTable(table);

        int i = 0;
        for (i = 0; i < dt.rows.size(); i++) {
            if (id.toString().equalsIgnoreCase(dt.rows.get(i).get("id").toString())) break;
        }

        // If id not found, throw error
        try {
            if (i >= dt.rows.size())
                throw new Exception("Could not find id " + id.toString() + " in table " + table + ".");


            Map<String, Object> dr = dt.rows.get(i);

            // Create DML Command
            switch (t) {
                case DELETE:
                    Delete d = new Delete(table);
                    d.addCondition("id", Condition.newCondition(ConditionType.EQ, id));
                    c.poseTransaction(new Transaction(d));
                    break;

                case INSERT:
                    RowConstant rc = new RowConstant();
                    for (DataColumn dc : dt.columns) {
                        if (dr.get(dc.getName()) == null) continue;
                        rc.addValue(dc.getName(), (Domain) dc.extendedProperties.get(Operator.domainFlag),
                                dr.get(dc.getName()));
                    }
                    Insert ins = new Insert(table, new ArrayList<String>(rc.exposedColumns), rc);
                    c.poseTransaction(new Transaction(ins));
                    break;

                case UPDATE:
                    rc = new RowConstant();
                    for (DataColumn dc : dt.columns) {
                        if (dc.getName().equals("id")) continue;
                        rc.addValue(dc.getName(), (Domain) dc.extendedProperties.get(Operator.domainFlag),
                                dr.get(dc.getName()));
                    }
                    Update up = new Update(table, new ArrayList<String>(rc.exposedColumns), rc);
                    up.addCondition("id", Condition.newCondition(ConditionType.EQ, id));
                    c.poseTransaction(new Transaction(up));
                    break;
            }
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
        return;
    }

    public static int createRow(String table, UUID id) {
        DataTable dt = ds.getTable(table);
        Map<String, Object> drNew = new HashMap<String, Object>();
        dt.rows.add(drNew);
        drNew.put("id", id);
        return dt.rows.size() - 1;
    }

    public static int createRow(String table, UUID id, UUID fk) {
        DataTable dt = ds.getTable(table);
        Map<String, Object> drNew = new HashMap<String, Object>();
        dt.rows.add(drNew);
        drNew.put("id", id);
        drNew.put("fk", fk);
        return dt.rows.size() - 1;
    }

    public static int fetchRow(String table, UUID id) {
        DataTable dt = ds.getTable(table);
        for (int i = 0; i < dt.rows.size(); i++) {
            if (id.toString().equalsIgnoreCase(dt.rows.get(i).get("id").toString())) return i;
        }

        try {
            if (!useDB)
                throw new Exception("Could not find selected row in memory");

            // create the appropriate query: select * from table where id = id
            Command com = new Command();
            String alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);

            for (int j = 0; j < dt.columns.size(); j++) {
                tr.addColumn(dt.columns.get(j).getName(), alias + "_" + dt.columns.get(j).getName(),
                        (Domain) dt.columns.get(j).extendedProperties.get(Operator.domainFlag));
            }
            Filter f = new Filter(tr);
            f.addCondition("id", Condition.newCondition(ConditionType.EQ, id));
            com.root = f;
            ResultSet res = c.poseQueryCommand(com);

            // fetch row
            res.next();
            Map<String, Object> drNew = new HashMap<String, Object>();
            for (int i = 0; i < dt.columns.size(); i++) {
                drNew.put(dt.columns.get(i).getName(), res.getObject(i + 1));
                //System.err.println(res.getObject(i + 1).toString());
            }
            dt.rows.add(drNew);
            return dt.rows.size() - 1;
        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
        return 0;
    }

    public static void deleteRow(String table, UUID id) {
        try {
            int i = fetchRow(table, id);

            // remove the row from memory
            ds.getTable(table).rows.remove(i);


            // generate delete statement
            if (useDB) {
                // generate nonquery object
                Delete d = new Delete(table);
                d.addCondition("id", Condition.newCondition(ConditionType.EQ, id));
                c.poseTransaction(new Transaction(d));
            }

        } catch (Exception e) {
            System.err.println("ERROR: " + e.toString());
        }
    }
}
